/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package onlineshopcode;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 *
 * @author Dean Kmet
 */
public class Database {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
           GUI panel = new GUI();
       panel.createLoginPanel();
    }
 
    /*
     * I finally get the database connection working. Now you can add and remove items from the db.
     * Next I will try to search in the db.
     * You cant use this code because it's from my local database. But you get the idea.
    */
    public void databaseMethod(){
               try {
         Connection c = DriverManager.getConnection("jdbc:mysql://localhost/products","root","root");
            Statement st = (Statement) c.createStatement();
            
            String productDes = "Pillow";
            int key = 4;
            int productPrice = 59;
            int itemNbr=4;
            
            /**************************************************************
                 Insert/Delete & update the "products" database!
             **************************************************************/
            String insertDB = "INSERT INTO testproduct VALUES ("+key+",'"+productDes+"',"+productPrice+","+itemNbr+")";    //you need the ' ' when it's a string
            // It look like this :  INSERT INTO testproduct VALUES (4,'Pillow',59,4);
            String deleteDB ="DELETE FROM testproduct WHERE ItemNumber =4";
           
            
            st.executeUpdate(insertDB);      // Update the database with your command
              c.close();
       }
       catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
    }
            public void searchInDataBase() {
        try {
            Connection c = DriverManager.getConnection("jdbc:mysql://localhost/products", "root", "root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM testproduct");
            int numberOfItems = 0;
            while (rs.next()) {
                int itemNumber = rs.getInt("ItemNumber");
                if (itemNumber == 3313) {                        // Item number 3313 is shoes
                    numberOfItems++;
                }
            }
            System.out.println(" Items in stock :" + numberOfItems); // Prints the number of items of that item-number.
            numberOfItems = 0;
            c.close();
            
             /*
            //prints out everything from eg table costumers (the content of colums, password and userName).
            ResultSet rs = st.executeQuery("SELECT * FROM costumers");7/ * means ALL.
            while (rs.next()) {
            int id = rs.getInt("password");//password is a column
            String firstName = rs.getString("userName");//userName is a column

            //print the results
            System.out.format("%s, %s \n", id, firstName);
            }
            st.close();
            */
            
        } catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
    }
            
        public int totalMembersPlusOne() {
               int numberOfMembers = 0;
        try {
            Connection c = DriverManager.getConnection("jdbc:mysql://localhost/products", "root", "root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM Members");
            
            while (rs.next()) {
               // int itemNumber = rs.getInt("idMembers");
                                                 
                    numberOfMembers++;
                
            }
            System.out.println(" Total members :" + numberOfMembers);
            numberOfMembers++;
          
            c.close();
        }
       
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
         return numberOfMembers;
           
    }
                public int shoesInStock() {
               int itemInStock = 0;
        try {
            Connection c = DriverManager.getConnection("jdbc:mysql://localhost/products", "root", "root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM testproduct");
            
            while (rs.next()) {
              int nbr = rs.getInt("InStock");
                 String name = rs.getString("Description");
                  //  int name = rs.getInt("Key");
                 
                if (name.equals("Shoes")) {                        // Item number 3313 is shoes
                  System.out.println(nbr);
                  return nbr;
                }   
                   System.out.println("Name: " + name + " and nbr " + nbr);
                    
                
            }
            System.out.println(" Shoes in stock :" + itemInStock);     
            c.close();
        }
       
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
         return itemInStock;
           
    }
                
                     public int sweaterInStock() {
               int itemInStock = 0;
        try {
            Connection c = DriverManager.getConnection("jdbc:mysql://localhost/products", "root", "root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM testproduct");
            
                 while (rs.next()) {
              int nbr = rs.getInt("InStock");
                 String name = rs.getString("Description");
                  //  int name = rs.getInt("Key");
                 
                if (name.equals("Sweater")) {                    
                  System.out.println(nbr);
                  return nbr;
                }   
                   System.out.println("Name: " + name + " and nbr " + nbr);                         
                    
                
            }
            System.out.println(" Sweater in stock :" + itemInStock);     
            c.close();
        }
       
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
         return itemInStock;
           
    }
                          public int jacketInStock() {
               int itemInStock = 0;
        try {
            Connection c = DriverManager.getConnection("jdbc:mysql://localhost/products", "root", "root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM testproduct");
            
                  while (rs.next()) {
              int nbr = rs.getInt("InStock");
                 String name = rs.getString("Description");
                  //  int name = rs.getInt("Key");
                 
                if (name.equals("Jacket")) {                     
                  System.out.println(nbr);
                  return nbr;
                }   
                   System.out.println("Name: " + name + " and nbr " + nbr);                          
   }
            System.out.println(" jacket in stock :" + itemInStock);     
            c.close();
        }
       
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
         return itemInStock;
           
    }
                               public int pillowInStock() {
               int itemInStock = 0;
        try {
            Connection c = DriverManager.getConnection("jdbc:mysql://localhost/products", "root", "root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM testproduct");
            
                  while (rs.next()) {
              int nbr = rs.getInt("InStock");
                 String name = rs.getString("Description");
                  //  int name = rs.getInt("Key");
                 
                if (name.equals("Pillow")) {                     
                  System.out.println(nbr);
                  return nbr;
                }   
                   System.out.println("Name: " + name + " and nbr " + nbr);                          
   }
            System.out.println(" chandles in stock :" + itemInStock);     
            c.close();
        }
       
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
         return itemInStock;
           
    }
                                    public int candlesInStock() {
               int itemInStock = 0;
        try {
            Connection c = DriverManager.getConnection("jdbc:mysql://localhost/products", "root", "root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM testproduct");
            
                  while (rs.next()) {
              int nbr = rs.getInt("InStock");
                 String name = rs.getString("Description");
                  //  int name = rs.getInt("Key");
                 
                if (name.equals("Scented Candles")) {                     
                  System.out.println(nbr);
                  return nbr;
                }   
                   System.out.println("Name: " + name + " and nbr " + nbr);                          
   }
            System.out.println(" chandles in stock :" + itemInStock);     
            c.close();
        }
       
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
         return itemInStock;
           
    }
   
                
                     public void deleteShoes() {
               int itemInStock = 0;
        try {
            Connection c = DriverManager.getConnection("jdbc:mysql://localhost/products", "root", "root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM testproduct");
            
      //      UPDATE `products`.`testproduct` SET `InStock`=44 WHERE `Key`='1';
int nr = shoesInStock()-1;
                        String deleteShoes ="UPDATE `products`.`testproduct` SET `InStock`="+nr+" WHERE `Key`='1'";
        
            
            st.executeUpdate(deleteShoes);      // Update the database with your command
   
            c.close();
        }
       
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
         
           
    }
      
                            public void deleteSweater() {
               int itemInStock = 0;
        try {
            Connection c = DriverManager.getConnection("jdbc:mysql://localhost/products", "root", "root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM testproduct");
            
      //      UPDATE `products`.`testproduct` SET `InStock`=44 WHERE `Key`='1';
int nr = sweaterInStock()-1;
                        String delete ="UPDATE `products`.`testproduct` SET `InStock`="+nr+" WHERE `Key`='2'";
        
            
            st.executeUpdate(delete);      // Update the database with your command
   
            c.close();
        }
       
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
         
           
    }
                            public void deleteShoesInsertVal(int id) {
        int itemInStock = 0;
        int nr = 0;
        try {
            Connection c = DriverManager.getConnection("jdbc:mysql://localhost/products", "root", "root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM testproduct");

            while (rs.next()) {                
                if (id ==rs.getInt("Key")) {
                    nr = rs.getInt("InStock") - 1; 
                }
            }
            
                String delete = "UPDATE `products`.`testproduct` SET `InStock`=" + nr + " WHERE `Key`=" + id;
                st.executeUpdate(delete);      // Update the database with your command
                c.close();
                
            } catch  (Exception e) {
            System.err.println("ERROR: " + e);
        }

    }
                                   public void deleteJacket() {
               int itemInStock = 0;
        try {
            Connection c = DriverManager.getConnection("jdbc:mysql://localhost/products", "root", "root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM testproduct");
            
      //      UPDATE `products`.`testproduct` SET `InStock`=44 WHERE `Key`='1';
int nr = jacketInStock()-1;
                        String delete ="UPDATE `products`.`testproduct` SET `InStock`="+nr+" WHERE `Key`='3'";
        
            
            st.executeUpdate(delete);      // Update the database with your command
   
            c.close();
        }
       
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
         
           
    }
                                          public void deletePillow() {
               int itemInStock = 0;
        try {
            Connection c = DriverManager.getConnection("jdbc:mysql://localhost/products", "root", "root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM testproduct");
            
      //      UPDATE `products`.`testproduct` SET `InStock`=44 WHERE `Key`='1';
int nr = pillowInStock()-1;
                        String delete ="UPDATE `products`.`testproduct` SET `InStock`="+nr+" WHERE `Key`='4'";
        
            
            st.executeUpdate(delete);      // Update the database with your command
   
            c.close();
        }
       
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
         
           
    }
                                                 public void deleteCandle() {
               int itemInStock = 0;
        try {
            Connection c = DriverManager.getConnection("jdbc:mysql://localhost/products", "root", "root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM testproduct");
            
      //      UPDATE `products`.`testproduct` SET `InStock`=44 WHERE `Key`='1';
int nr = candlesInStock()-1;
                        String delete ="UPDATE `products`.`testproduct` SET `InStock`="+nr+" WHERE `Key`='5'";
        
            
            st.executeUpdate(delete);      // Update the database with your command
   
            c.close();
        }
       
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
         
           
    }
}
